5.0 数据库完整性详解（PRIMARY KEY、REFERENCES、CHECK、CONSTRAINT、DOMAIN、TRIGGER）

您所在的位置：网站首页 › 数据库foreign key reference › 5.0 数据库完整性详解（PRIMARY KEY、REFERENCES、CHECK、CONSTRAINT、DOMAIN、TRIGGER）

5.0 数据库完整性详解（PRIMARY KEY、REFERENCES、CHECK、CONSTRAINT、DOMAIN、TRIGGER）

2023-10-03 16:40| 来源: 网络整理| 查看: 265

文章目录 0.思维导图1.数据库完整性概述2.实体完整性---PRIMARY KEY（1）实体完整性的定义（2）实体完整性检查和违约处理 2.参照完整性---REFERENCES（1）参照完整性定义（2）参照完整性检查和违约处理 3.用户定义的完整性---CHECK（1）属性上的约束条件定义（2）属性上的约束条件检查和处理（3）元组上的约束条件的定义（4）元组上的约束条件检查和违约处理 4.完整性约束命名子句---CONSTRAINT5.域中的完整性限制---DOMAIN6.断言---ASSERTION（1）定义断言（2）删除断言 7.触发器---TRIGGER（1）定义触发器（2）激活触发器（3）删除触发器

0.思维导图

在这里插入图片描述

1.数据库完整性概述

简单介绍：

首先先概述一下数据库完整性指的是什么，数据库完整性指的是数据的正确性和相容性。数据的正确性是指数据是符合现实世界语义、反映当前实际状况的;比如说人类的性别，只能是男和女。数据的相容性是指数据库同一对象在不同关系表中的数据是符合逻辑的。比如说年龄一般都在1-100岁，当然也有超过一百岁的，反正没有两百岁，三百岁成仙的人类。

既然我们学的是数据库，那么数据库管理系统就应该为数据完整性实现如下的功能：

1.提供定义完整性约束条件的机制

完整约束条件也称完整性规则，是数据库中数据必须满足的语义条件规则为保证数据的正确、有效和相容性的一些规则数据的主码、外码、一些约束规则

2.提供完整性检查的方法

数据库管理系统中检查数据是否满足完整性规则的机制称为完整性检查一般在执行INSERT、UPDATE、DELETE时检查

3.违约处理

数据库管理系统若发现用户的操作违背了完整性约束条件将采取一定的动作，如拒绝(NO ACTION)执行该操作或级联(CASCADE)执行其他操作，进行违约处理以保证数据的完整性。

此前写的一篇文章提到了关系完整性约束的基本概念关系完整性的基本概念，而本篇文章讲的是利用SQL如何去实现这些约束机制，如何实现完整性规则

2.实体完整性—PRIMARY KEY （1）实体完整性的定义

关系模型的实体完整性

CREATE TABLE中用PRIMARY KEY定义

单属性构成的码有两种说明方法

定义为列级约束条件定义为表级约束条件

对多个属性构成的码只有一种说明方法

定义为表级约束条件

举几个例子

将Student表中的Sno属性定义为码 (1)在列级定义主码 CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY， Sname CHAR(20) NOT NULL， Ssex CHAR(2) ， Sage SMALLINT， Sdept CHAR(20)); (2)在表级定义主码 CREATE TABLE Student (Sno CHAR(9)， Sname CHAR(20) NOT NULL， Ssex CHAR(2) ， Sage SMALLINT， Sdept CHAR(20)， PRIMARY KEY (Sno) ); 将SC表中的Sno，Cno属性组定义为码 CREATE TABLE SC (Sno CHAR(9) NOT NULL， Cno CHAR(4) NOT NULL， Grade SMALLINT， PRIMARY KEY (Sno，Cno) /*只能在表级定义主码*/ ); （2）实体完整性检查和违约处理

插入或对主码列进行更新操作时，RDBMS按照实体完整性规则自动进行检查。包括：

检查主码值是否唯一，如果不唯一则拒绝插入或修改检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改

检查记录中主码值是否唯一的一种方法是进行全表扫描在这里插入图片描述

全表扫描是十分耗时的。为了避免对基本表进行全表扫描，关系数据库管理系统一般都在主码上自动建立一个索引，如图5.2的B+树索引，通过索引查找基本表中是否已经存在新的主码值将大大提高效率。例如，如果新插入记录的主码值是25，通过主码索引，从B+树的根结点开始查找，只要读取三个结点就可以知道该主码值已经存在，所以不能插入这条记录。这三个结点是根结点(51)、中间结点(1230)和叶结点(15 20 25)。

在这里插入图片描述

2.参照完整性—REFERENCES （1）参照完整性定义

关系模型的参照完整性定义

在CREATE TABLE中用FOREIGN KEY短语定义哪些列为外码用REFERENCES短语指明这些外码参照哪些表的主码

举个例子

例如，关系SC中一个元组表示一个学生选修的某门课程的成绩，（Sno，Cno）是主码。 Sno，Cno分别参照引用Student表的主码和Course表的主码定义SC中的参照完整性 CREATE TABLE SC (Sno CHAR(9) NOT NULL， Cno CHAR(4) NOT NULL， Grade SMALLINT， PRIMARY KEY (Sno， Cno)， /*在表级定义实体完整性*/ FOREIGN KEY (Sno) REFERENCES Student(Sno)， /*在表级定义参照完整性*/ FOREIGN KEY (Cno) REFERENCES Course(Cno) /*在表级定义参照完整性*/ ); （2）参照完整性检查和违约处理

在这里插入图片描述

参照完整性违约处理

拒绝(NO ACTION)执行默认策略

级联(CASCADE)操作

设置为空值（SET-NULL）对于参照完整性，除了应该定义外码，还应定义外码列是否允许空值

［例4］显式说明参照完整性的违约处理示例 CREATE TABLE SC (Sno CHAR(9) NOT NULL， Cno CHAR(4) NOT NULL， Grade SMALLINT， PRIMARY KEY（Sno，Cno）， FOREIGN KEY (Sno) REFERENCES Student(Sno) ON DELETE CASCADE /*级联删除SC表中相应的元组*/ ON UPDATE CASCADE， /*级联更新SC表中相应的元组*/ FOREIGN KEY (Cno) REFERENCES Course(Cno) ON DELETE NO ACTION /*当删除course 表中的元组造成了与SC表不一致时拒绝删除*/ ON UPDATE CASCADE /*当更新course表中的cno时，级联更新SC表中相应的元组*/ )；经过测试，当UPDATE更新Student和Course表数据时，SC也自动更新当删除Student的数据时，SC同步删除相应数据当删除Course的数据时，拒绝删除，因为SC中有相应的外码数据，并设置了NO ACTION当删除SC的数据时，对Student和Course无影响

在这里插入图片描述

3.用户定义的完整性—CHECK 用户定义的完整性就是针对某一具体应用的数据必须满足的语义要求RDBMS提供，而不必由应用程序承担（1）属性上的约束条件定义

CREATE TABLE时定义

列值非空（NOT NULL）列值唯一（UNIQUE）检查列值是否满足一个布尔表达式（CHECK） 1.不允许取空值［例5］在定义SC表时，说明Sno、Cno、Grade属性不允许取空值。 CREATE TABLE SC (Sno CHAR(9) NOT NULL， Cno CHAR(4) NOT NULL， Grade SMALLINT NOT NULL， PRIMARY KEY (Sno， Cno)， /* 如果在表级定义实体完整性，隐含了Sno，Cno不允许取空值，则在列级不允许取空值的定义就不必写了 */ ）; 2.列值唯一［例6］建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码 CREATE TABLE DEPT (Deptno NUMERIC(2)， Dname CHAR(9) UNIQUE，/*要求Dname列值唯一*/ Location CHAR(10)， PRIMARY KEY (Deptno) )； 3. 用CHECK短语指定列值应该满足的条件［例7］Student表的Ssex只允许取“男”或“女”。 CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY， Sname CHAR(8) NOT NULL， Ssex CHAR(2) CHECK (Ssex IN (‘男’，‘女’) ) ， /*性别属性Ssex只允许取'男'或'女' */ Sage SMALLINT， Sdept CHAR(20) ); （2）属性上的约束条件检查和处理插入元组或修改属性的值时，RDBMS检查属性上的约束条件是否被满足如果不满足则操作被拒绝执行（3）元组上的约束条件的定义在CREATE TABLE时可以用CHECK短语定义元组上的约束条件，即元组级的限制同属性值限制相比，元组级的限制可以设置不同属性之间的取值的相互约束条件［例9］当学生的性别是男时，其名字不能以Ms.打头。 CREATE TABLE Student (Sno CHAR(9)， Sname CHAR(8) NOT NULL， Ssex CHAR(2)， Sage SMALLINT， Sdept CHAR(20)， PRIMARY KEY (Sno)， CHECK (Ssex='女' OR Sname NOT LIKE 'Ms.%') /*定义了元组中Sname和 Ssex两个属性值之间的约束条件*/ )；性别是女性的元组都能通过该项检查，因为Ssex=‘女’成立；当性别是男性时，要通过检查则名字一定不能以Ms.打头（4）元组上的约束条件检查和违约处理插入元组或修改属性的值时，RDBMS检查元组上的约束条件是否被满足如果不满足则操作被拒绝执行 4.完整性约束命名子句—CONSTRAINT

CONSTRAINT 约束语句格式：

CONSTRAINT ［PRIMARY KEY短语 |FOREIGN KEY短语 |CHECK短语］

举个例子：

［例10］建立学生登记表Student，要求学号在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。 CREATE TABLE Student (Sno NUMERIC(6) CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999)， Sname CHAR(20) CONSTRAINT C2 NOT NULL， Sage NUMERIC(3) CONSTRAINT C3 CHECK (Sage =ALL( SELECT count (*)/*此断言的谓词，涉及聚集操作count */ FROM SC /*和分组函数group by的SQL语句*/ GROUP by cno ); [例5.20 限制每个学期每一门课程最多60名学生选修] 首先修改SC表的模式，增加一个“学期(TERM)”的属性。 ALTER TABLE SC ADD TERM DATE; /*先修改SC表，增加TERM属性，它的类型是DATE*/ 然后定义断言: CREATE ASSERTION ASSE_SC_CNUM2 CHECK (60>=ALL ( select count (*) from SC group by Cno,TERM )); （2）删除断言

DROP ASSERTION ;

如果断言很复杂，则系统在检测和维护断言上的开销较高，这是在使用断言时应该注意的。 7.触发器—TRIGGER 触发器（Trigger）是用户定义在关系表上的一类由事件驱动的特殊过程由服务器自动激活可以进行更为复杂的检查和操作，具有更精细和更强大的数据控制能力（1）定义触发器

CREATE TRIGGER语法格式:

CREATE TRIGGER {BEFORE | AFTER} ON FOR EACH {ROW | STATEMENT} ［WHEN ］

定义触发器的语法说明:

创建者：表的拥有者触发器名表名：触发器的目标表,不能建立在视图上触发事件：INSERT、DELETE、UPDATE触发器类型行级触发器（FOR EACH ROW）; 语句级触发器（FOR EACH STATEMENT）;

在这里插入图片描述

例如,假设在［例11］的TEACHER表上创建了一个AFTER UPDATE触发器。如果表TEACHER有1000行，执行如下语句： UPDATE TEACHER SET Deptno=5;如果该触发器为语句级触发器，那么执行完该语句后，触发动作只发生一次如果是行级触发器，触发动作将执行1000次

触发条件触发条件为真，则执行触发动作体，否则不执行; 省略WHEN触发条件时，只要触发器激活（触发器事件）则立刻执行触发动作体;

触发动作体触发动作体可以是一个匿名PL/SQL过程块; 也可以是对已创建存储过程的调用;

［例18］定义一个BEFORE行级触发器，为教师表Teacher定义完整性规则“教授的工资不得低于4000元，如果低于4000元，自动改为4000元”。 CREATE TRIGGER Insert_Or_Update_Sal BEFORE INSERT OR UPDATE ON Teacher /*触发事件是插入或更新操作*/ FOR EACH ROW /*行级触发器*/ AS BEGIN /*定义触发动作体，是PL/SQL过程块*/ IF (new.Job='教授') AND (new.Sal

【本文地址】

公司简介

联系我们